package com.childenglish.mapper;

import com.childenglish.entity.SysUser;
import org.apache.ibatis.annotations.*;

import java.util.List;

@Mapper
public interface SysUserMapper {

    @Select("SELECT u.*, r.role_key as user_role " +
            "FROM sys_user u " +
            "LEFT JOIN sys_user_role ur ON u.user_id = ur.user_id " +  // 修正表连接
            "LEFT JOIN sys_role r ON ur.role_id = r.role_id " +       // 修正表连接
            "WHERE u.user_name = #{username}")
    @Results({
            @Result(property = "id", column = "user_id"),
            @Result(property = "username", column = "user_name"),
            @Result(property = "nickname", column = "nick_name"),
            @Result(property = "phone", column = "phonenumber"),
            @Result(property = "role", column = "user_role"),         // 修正角色字段映射
            @Result(property = "createdTime", column = "create_time")
    })
    SysUser findByUsername(@Param("username") String username);

    @Select("SELECT u.*, r.role_key as user_role " +
            "FROM sys_user u " +
            "LEFT JOIN sys_user_role ur ON u.user_id = ur.user_id " +
            "LEFT JOIN sys_role r ON ur.role_id = r.role_id " +
            "WHERE u.user_id = #{id}")
    @Results({
            @Result(property = "id", column = "user_id"),
            @Result(property = "username", column = "user_name"),
            @Result(property = "nickname", column = "nick_name"),
            @Result(property = "phone", column = "phonenumber"),
            @Result(property = "role", column = "user_role"),
            @Result(property = "createdTime", column = "create_time")
    })
    SysUser findById(@Param("id") Long id);

    /**
     * 插入新用户
     * 注意：角色信息通过 sys_user_role 关联表管理，不在此表中存储
     */
    @Insert("INSERT INTO sys_user (user_name, password, nick_name, user_type, email, phonenumber, create_time) " +
            "VALUES (#{username}, #{password}, #{nickname}, #{userType}, #{email}, #{phone}, #{createdTime})")
    @Options(useGeneratedKeys = true, keyProperty = "id")
    int insert(SysUser user);

    @Select("SELECT COUNT(*) FROM sys_user WHERE user_name = #{username}")
    int countByUsername(String username);

    @Select("SELECT COUNT(*) FROM sys_user WHERE email = #{email}")
    int countByEmail(String email);

    @Select("SELECT COUNT(*) FROM sys_user WHERE phonenumber = #{phone}")
    int countByPhone(String phone);

    /**
     * 根据邮箱查找用户
     */
    @Select("SELECT u.*, r.role_key as user_role " +
            "FROM sys_user u " +
            "LEFT JOIN sys_user_role ur ON u.user_id = ur.user_id " +
            "LEFT JOIN sys_role r ON ur.role_id = r.role_id " +
            "WHERE u.email = #{email}")
    @Results({
            @Result(property = "id", column = "user_id"),
            @Result(property = "username", column = "user_name"),
            @Result(property = "nickname", column = "nick_name"),
            @Result(property = "phone", column = "phonenumber"),
            @Result(property = "role", column = "user_role"),
            @Result(property = "createdTime", column = "create_time")
    })
    SysUser findByEmail(@Param("email") String email);

    /**
     * 根据手机号查找用户
     */
    @Select("SELECT u.*, r.role_key as user_role " +
            "FROM sys_user u " +
            "LEFT JOIN sys_user_role ur ON u.user_id = ur.user_id " +
            "LEFT JOIN sys_role r ON ur.role_id = r.role_id " +
            "WHERE u.phonenumber = #{phone}")
    @Results({
            @Result(property = "id", column = "user_id"),
            @Result(property = "username", column = "user_name"),
            @Result(property = "nickname", column = "nick_name"),
            @Result(property = "phone", column = "phonenumber"),
            @Result(property = "role", column = "user_role"),
            @Result(property = "createdTime", column = "create_time")
    })
    SysUser findByPhone(@Param("phone") String phone);

    /**
     * 更新用户密码
     */
    @Update("UPDATE sys_user SET password = #{password} WHERE user_id = #{userId}")
    int updatePassword(@Param("userId") Long userId, @Param("password") String password);

    /**
     * 查询所有用户（包含角色信息）
     */
    @Select("SELECT u.*, r.role_key as user_role " +
            "FROM sys_user u " +
            "LEFT JOIN sys_user_role ur ON u.user_id = ur.user_id " +
            "LEFT JOIN sys_role r ON ur.role_id = r.role_id " +
            "ORDER BY u.create_time DESC")
    @Results({
            @Result(property = "id", column = "user_id"),
            @Result(property = "username", column = "user_name"),
            @Result(property = "nickname", column = "nick_name"),
            @Result(property = "phone", column = "phonenumber"),
            @Result(property = "role", column = "user_role"),
            @Result(property = "createdTime", column = "create_time")
    })
    List<SysUser> findAll();

    /**
     * 根据角色查询用户
     */
    @Select("SELECT u.*, r.role_key as user_role " +
            "FROM sys_user u " +
            "LEFT JOIN sys_user_role ur ON u.user_id = ur.user_id " +
            "LEFT JOIN sys_role r ON ur.role_id = r.role_id " +
            "WHERE r.role_key = #{roleKey} " +
            "ORDER BY u.create_time DESC")
    @Results({
            @Result(property = "id", column = "user_id"),
            @Result(property = "username", column = "user_name"),
            @Result(property = "nickname", column = "nick_name"),
            @Result(property = "phone", column = "phonenumber"),
            @Result(property = "role", column = "user_role"),
            @Result(property = "createdTime", column = "create_time")
    })
    List<SysUser> findByRole(@Param("roleKey") String roleKey);

    /**
     * 更新用户信息
     */
    @Update("UPDATE sys_user SET " +
            "user_name = #{username}, " +
            "nick_name = #{nickname}, " +
            "email = #{email}, " +
            "phonenumber = #{phone} " +
            "WHERE user_id = #{id}")
    int updateUser(SysUser user);

    /**
     * 删除用户
     */
    @Delete("DELETE FROM sys_user WHERE user_id = #{userId}")
    int deleteById(@Param("userId") Long userId);
}